import sqlite3
import os
import shutil
from datetime import datetime, timedelta

DB_path = "./data/tasks.db"
backup_time_file = './data/backup/last_backup_time.txt'

def init_DB():
    if not os.path.exists(DB_path):
        conn = sqlite3.connect(DB_path)
        c = conn.cursor()
        c.execute('''CREATE TABLE IF NOT EXISTS tasks
                     (id INTEGER PRIMARY KEY, 
                     title TEXT, 
                     category TEXT, 
                     priority INTEGER, 
                     due_date TEXT, 
                     status TEXT DEFAULT '未完成')''')
        sample_tasks = [
            ('完成Python项目', '学习', 1, '2023-12-31', '未完成'),
            ('编写报告', '工作', 2, '2023-11-30', '未完成'),
            ('购物', '生活', 3, '2023-11-15', '未完成')
            ]
        for task in sample_tasks:
            c.execute("INSERT INTO tasks (title, category, priority, due_date, status) VALUES (?, ?, ?, ?, ?)", task)
        conn.commit()
        conn.close()

def backup_DB():
    now = datetime.now()
    if os.path.exists(backup_time_file):
        with open(backup_time_file, 'r') as file:
            last_backup_time_str = file.read().strip()
            last_backup_time = datetime.strptime(last_backup_time_str, "%Y-%m-%d_%H-%M-%S")
    else:
        last_backup_time = None
    if last_backup_time is None or (now - last_backup_time) > timedelta(hours=3):
        backup_dir = now.strftime("%Y-%m-%d")
        backup_time_str = now.strftime("%Y-%m-%d_%H-%M-%S")
        if not os.path.exists(f'./data/backup/{backup_dir}'):
            os.makedirs(f'./data/backup/{backup_dir}')
        backup_path = os.path.join(f'./data/backup/{backup_dir}', f'daily_records_backup_{backup_time_str}.db')
        if not os.path.exists(backup_path):
            shutil.copy(DB_path, backup_path)
            with open(backup_time_file, 'w') as file:
                file.write(backup_time_str)
            print(f'Backup created at {backup_path}')
        if not os.path.exists(backup_dir):
            pass
        else:
            for filename in os.listdir(backup_dir):
                file_path = os.path.join(backup_dir, filename)
                if os.path.isfile(file_path):
                    file_mtime = datetime.fromtimestamp(os.path.getmtime(file_path))
                    if (now - file_mtime) > timedelta(weeks=1):
                        os.remove(file_path)
                        print(f'Deleted old backup: {file_path}')

def read_DB():
    conn = sqlite3.connect(DB_path)
    c = conn.cursor()
    c.execute("SELECT * FROM tasks ORDER BY category")
    tasks = c.fetchall()
    conn.close()
    return tasks

def add_task(title, category, priority, due_date):
    conn = sqlite3.connect(DB_path)
    c = conn.cursor()
    c.execute("INSERT INTO tasks (title, category, priority, due_date) VALUES (?, ?, ?, ?)",
              (title, category, priority, due_date))
    conn.commit()
    conn.close()

def delete_task(task_id):
    conn = sqlite3.connect(DB_path)
    c = conn.cursor()
    c.execute("DELETE FROM tasks WHERE id=?", (task_id,))
    c.execute("SELECT id FROM tasks ORDER BY id")
    task_ids = [row[0] for row in c.fetchall()]
    for index, task_id in enumerate(task_ids, start=1):
        if task_id != index:
            c.execute("UPDATE tasks SET id=? WHERE id=?", (index, task_id))
    conn.commit()
    conn.close()

def set_task_completed(task_id):
    conn = sqlite3.connect(DB_path)
    c = conn.cursor()
    c.execute("UPDATE tasks SET status='已完成' WHERE id=?", (task_id,))
    conn.commit()
    conn.close()

def update_task(task_id, title=None, category=None, priority=None, due_date=None, status=None):
    conn = sqlite3.connect(DB_path)
    c = conn.cursor()
    if title is not None:
        c.execute("UPDATE tasks SET title=? WHERE id=?", (title, task_id))
    if category is not None:
        c.execute("UPDATE tasks SET category=? WHERE id=?", (category, task_id))
    if priority is not None:
        c.execute("UPDATE tasks SET priority=? WHERE id=?", (priority, task_id))
    if due_date is not None:
        c.execute("UPDATE tasks SET due_date=? WHERE id=?", (due_date, task_id))
    if status is not None:
        c.execute("UPDATE tasks SET status=? WHERE id=?", (status, task_id))
    conn.commit()
    conn.close()

if __name__ == "__main__":
    init_DB()
    # add_task("完成Python项目", "学习", 2, "2023-12-11")
    # delete_task(1)
    backup_DB()
    # set_task_completed(3)
    tasks = read_DB()
    for task in tasks:
        print(task)